﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

// Configure log4net using the .config file
[assembly: log4net.Config.XmlConfigurator(ConfigFile = "log4net.cfg.xml", Watch = true)]


namespace FixOrderServiceDeliverDetail
{
    /// <summary>
    /// 本程序主要用于修复订单的业务受理过程明细中因多次插入业务受理成功结果导致的对账不平
    /// </summary>
    public class Program
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        static void Main(string[] args)
        {
            log.Info("开始处理重复明细数据");
            MergeRemarks();
            log.Info("处理重复数据完毕");

            Console.Read();
        }

        public static List<string> GetServiceDeliverIDs()
        {
            var serviceDeliverIDs = new List<string>();

            try
            {
                const string sql = "select * from tempdb.dbo.DoubleService0519";

                var sqlconn = new SqlConnection();
                sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["ECSSDb"].ConnectionString;

                var sqlcmd = new SqlCommand();
                sqlcmd.Connection = sqlconn;
                sqlcmd.CommandText = sql;

                try
                {
                    sqlcmd.Connection.Open();
                    var sqlrd = sqlcmd.ExecuteReader();
                    while (sqlrd.Read())
                    {
                        serviceDeliverIDs.Add(sqlrd["servicedeliverid"].ToString());
                    }

                }
                finally
                {
                    sqlcmd.Connection.Close();
                }
            }
            catch (Exception ex)
            {
                log.Debug("获取重复的ServiceDeliverID出错", ex);
            }

            return serviceDeliverIDs;
        }

        public static void MergeRemarks()
        {
            try
            {
                var ids = GetServiceDeliverIDs();
                foreach (string id in ids)
                {
                    var remarks = string.Empty;

                    #region 获取所有成功记录的备注信息
                    string sql = @"select deliverremark from orderservciedeliverdetail where servicedeliverid = @servicedeliverid and deliverresult=1 order by detailID";
                    var sqlconn = new SqlConnection();
                    sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["ECSSDb"].ConnectionString;

                    var sqlcmd = new SqlCommand();
                    sqlcmd.Connection = sqlconn;
                    sqlcmd.CommandText = sql;

                    sqlcmd.Parameters.Add("@servicedeliverid", SqlDbType.Int);
                    sqlcmd.Parameters["@servicedeliverid"].Value = Convert.ToInt32(id);

                    try
                    {
                        sqlcmd.Connection.Open();
                        var sqlrd = sqlcmd.ExecuteReader();
                        while (sqlrd.Read())
                        {
                            remarks = remarks + sqlrd["deliverremark"].ToString() + "，";
                        }
                    }
                    finally
                    {
                        sqlcmd.Connection.Close();
                    }
                    #endregion

                    #region 获取成功的第一条记录编号
                    int topid;
                    sql = @"select top 1 detailid from orderservciedeliverdetail where servicedeliverid = @servicedeliverid and deliverresult=1 order by detailID";

                    sqlcmd = new SqlCommand();
                    sqlcmd.Connection = sqlconn;
                    sqlcmd.CommandText = sql;

                    sqlcmd.Parameters.Add("@servicedeliverid", SqlDbType.Int);
                    sqlcmd.Parameters["@servicedeliverid"].Value = Convert.ToInt32(id);

                    try
                    {
                        sqlcmd.Connection.Open();
                        topid = Convert.ToInt32(sqlcmd.ExecuteScalar());
                    }
                    finally
                    {
                        sqlcmd.Connection.Close();
                    }
                    #endregion

                    #region 将所有的成功备注都汇集到第一条成功记录中去
                    sql = "update orderservciedeliverdetail set deliverremark=@remark where DetailID=@detailID";
                    sqlcmd = new SqlCommand();
                    sqlcmd.Connection = sqlconn;
                    sqlcmd.CommandText = sql;

                    sqlcmd.Parameters.Add("@detailID", SqlDbType.Int);
                    sqlcmd.Parameters["@detailID"].Value = Convert.ToInt32(topid);

                    sqlcmd.Parameters.Add("@remark", SqlDbType.NVarChar, 512);
                    sqlcmd.Parameters["@remark"].Value = remarks;

                    try
                    {
                        sqlcmd.Connection.Open();
                        sqlcmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        sqlcmd.Connection.Close();
                    }
                    #endregion

                    #region 删除多余的重复成功记录
                    sql = "delete from orderservciedeliverdetail where servicedeliverid = @servicedeliverid and deliverresult=1 and detailid<>@detailID";
                    sqlcmd = new SqlCommand();
                    sqlcmd.Connection = sqlconn;
                    sqlcmd.CommandText = sql;

                    sqlcmd.Parameters.Add("@servicedeliverid", SqlDbType.Int);
                    sqlcmd.Parameters["@servicedeliverid"].Value = Convert.ToInt32(id);

                    sqlcmd.Parameters.Add("@detailID", SqlDbType.Int);
                    sqlcmd.Parameters["@detailID"].Value = Convert.ToInt32(topid);

                    try
                    {
                        sqlcmd.Connection.Open();
                        sqlcmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        sqlcmd.Connection.Close();
                    }
                    #endregion

                    log.Info(string.Format("处理ServiceDeliverID为：{0}的记录成功", id));

                }
            }
            catch (Exception ex)
            {
                log.Debug("合并备注信息出错", ex);
            }
        }
    }
}
